package com.hctforgreen.greenservice.db;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.hctforgreen.greenservice.model.MessagesListEntity;
import com.hctforgreen.greenservice.model.MessagesListEntity.MessagesEntity;

/**
 * 数据库操作类
 * 
 * @author ld 20131122
 * 
 */
public class DBMessageManager {
	private DBHelper helper;
	private SQLiteDatabase db;
	private static final String TABLE_MESSAGE = "table_message";

	public DBMessageManager(Context context) {
		helper = new DBHelper(context);
		// 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0,
		// mFactory);
		// 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
		db = helper.getWritableDatabase();
	}

	/**
	 * add messages
	 * 
	 * @param msgs
	 */
	public void add(List<MessagesListEntity.MessagesEntity> msgs) {
		db.beginTransaction(); // 开始事务
		try {
			for (MessagesEntity msg : msgs) {

				if (!existsMsg(msg)) {
					db.execSQL("INSERT INTO " + TABLE_MESSAGE
							+ " VALUES(?, ?, ?, ?, ?,?)", new Object[] {
							msg.id, msg.date, msg.seriesName, msg.readStatus,
							msg.recentMsg, msg.msgTitle });
				}

			}
			db.setTransactionSuccessful(); // 设置事务成功完成

		} catch (Exception e) {// 主键冲突异常
			e.printStackTrace();
		} finally {
			db.endTransaction(); // 结束事务
		}
	}

	/**
	 * add messages
	 * 
	 * @param msgs
	 */
	public void delete(List<MessagesListEntity.MessagesEntity> msgs) {
		db.beginTransaction(); // 开始事务
		try {
			for (MessagesEntity msg : msgs) {
				db.execSQL("delete from " + TABLE_MESSAGE + " where id=? ",
						new Object[] { msg.id });

			}
			db.setTransactionSuccessful(); // 设置事务成功完成

		} catch (Exception e) {// 主键冲突异常
			e.printStackTrace();
		} finally {
			db.endTransaction(); // 结束事务
		}
	}

	private boolean existsMsg(MessagesEntity msg) {
		Cursor c = db.rawQuery("select id from " + TABLE_MESSAGE
				+ " where id=?", new String[] { msg.id });
		if (c == null || c.getCount() == 0) {
			// Log.w("ss", c.getCount());
			return false;
		}
		return c.getCount() > 0;

	}

	/**
	 * 获取未读消息条数
	 * 
	 * @return
	 */
	public Integer getUnreadMessageCount() {
		Integer count = 0;
		Cursor cursor = db.rawQuery("SELECT COUNT(id) countNum FROM "
				+ TABLE_MESSAGE + " WHERE readStatus=0 and recentMsg=1", null);
		if (cursor != null && cursor.getColumnCount() > 0) {
			cursor.moveToFirst();
			count = cursor.getInt(cursor.getColumnIndex("countNum"));
			cursor.close();
		}
		return count;

	}

	/**
	 * add message
	 * 
	 * @param msg
	 */
	public void add(MessagesEntity msg) {
		if (existsMsg(msg)) {
			return;
		}

		db.beginTransaction(); // 开始事务
		try {
			db.execSQL("INSERT INTO " + TABLE_MESSAGE
					+ " VALUES(?, ?, ?, ?, ?,?)", new Object[] { msg.id,
					msg.date, msg.seriesName, msg.readStatus, msg.recentMsg,
					msg.msgTitle });
			db.setTransactionSuccessful(); // 设置事务成功完成
		} finally {
			db.endTransaction(); // 结束事务
		}
	}

	public void updateMsg(MessagesEntity msg) {
		db.beginTransaction(); // 开始事务
		try {
			db.execSQL("UPDATE " + TABLE_MESSAGE + " set readStatus=? ,date=?"
					+ "where id=?", new Object[] { msg.readStatus, msg.date,
					msg.id });
			db.setTransactionSuccessful(); // 设置事务成功完成
		} finally {
			db.endTransaction(); // 结束事务
		}

	}

	public MessagesEntity setMessageReadStatus(MessagesEntity entity) {
		if (entity == null) {
			return entity;
		}
		try {
			Cursor cursor = db
					.rawQuery("SELECT readStatus rs FROM " + TABLE_MESSAGE
							+ " WHERE id=?", new String[] { entity.id });
			if (cursor != null && cursor.getColumnCount() > 0) {
				cursor.moveToFirst();
				entity.readStatus = cursor.getInt(cursor.getColumnIndex("rs"));
				cursor.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return entity;
	}

	// /**
	// * update warn's content
	// *
	// * @param person
	// */
	// public void updateAge(PwdEntity pwd) {
	// ContentValues cv = new ContentValues();
	// // cv.put("content", pwd.Content);
	// // cv.put("day", pwd.day);
	// // db.update(TABLE_WARN, cv, "_id = ?", new
	// // String[]{String.valueOf(warn._id)});
	// }

	/**
	 * delete old warn
	 * 
	 * @param person
	 */
	public void deleteOldPwd(MessagesEntity pwd) {
		db.delete(TABLE_MESSAGE, "id = ?",
				new String[] { String.valueOf(pwd.id) });
	}

	/**
	 * query all warns, return list
	 * 
	 * @return List<MessagesEntity>
	 */
	public List<MessagesEntity> queryAll() {
		ArrayList<MessagesEntity> msgs = new ArrayList<MessagesEntity>();
		Cursor c = queryTheCursor();
		while (c.moveToNext()) {
			MessagesEntity msg = new MessagesEntity();
			msg.id = c.getString(c.getColumnIndex("id"));
			msg.date = c.getString(c.getColumnIndex("date"));
			msg.msgTitle = c.getString(c.getColumnIndex("msgTitle"));
			msg.readStatus = c.getInt(c.getColumnIndex("readStatus"));
			msg.seriesName = c.getString(c.getColumnIndex("seriesName"));
			msgs.add(msg);
		}
		c.close();
		return msgs;
	}

	/**
	 * 取六条最近消息
	 * 
	 * @return
	 */
	public List<MessagesEntity> queryThreeMsg() {
		ArrayList<MessagesEntity> msgs = new ArrayList<MessagesListEntity.MessagesEntity>();

		try {
			Cursor c = db.rawQuery("SELECT * FROM " + TABLE_MESSAGE
					+ " where recentMsg=1 order by date desc limit 0,6", null);
			while (c.moveToNext()) {
				MessagesEntity msg = new MessagesEntity();
				msg.id = c.getString(c.getColumnIndex("id"));
				msg.date = c.getString(c.getColumnIndex("date"));
				msg.msgTitle = c.getString(c.getColumnIndex("msgTitle"));
				msg.readStatus = c.getInt(c.getColumnIndex("readStatus"));
				msg.recentMsg = c.getInt(c.getColumnIndex("recentMsg"));
				msg.seriesName = c.getString(c.getColumnIndex("seriesName"));
				msgs.add(msg);
			}
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			// return msgs;
		}
		return msgs;
	}

	/**
	 * query all warns, return cursor
	 * 
	 * @return Cursor
	 */
	public Cursor queryTheCursor() {
		Cursor c = db.rawQuery("SELECT * FROM " + TABLE_MESSAGE
				+ " where recentMsg=1  order by date desc", null);
		return c;
	}

	//
	// /**
	// * query all warns, return list
	// *
	// * @return List<Person>
	// */
	// public List<PwdEntity> queryByDate(String date) {
	// ArrayList<PwdEntity> pwds = new ArrayList<PwdEntity>();
	// Cursor c = fetchFromDate(date);
	// while (c.moveToNext()) {
	// PwdEntity pwd = new PwdEntity();
	// pwd._id = c.getInt(c.getColumnIndex("_id"));
	// pwd.stripeCode = c.getString(c.getColumnIndex("stripeCode"));
	// pwd.openPassword = c.getString(c.getColumnIndex("openPassword"));
	// pwd.projectName = c.getString(c.getColumnIndex("projectName"));
	// pwd.ownerName = c.getString(c.getColumnIndex("ownerName"));
	// pwd.ownerPhone = c.getString(c.getColumnIndex("ownerPhone"));
	// pwd.note = c.getString(c.getColumnIndex("note"));
	// pwd.date = c.getString(c.getColumnIndex("date"));
	// pwd.time = c.getString(c.getColumnIndex("time"));
	// pwd.province = c.getString(c.getColumnIndex("province"));
	// pwd.city = c.getString(c.getColumnIndex("city"));
	// pwd.district = c.getString(c.getColumnIndex("district"));
	// pwd.street = c.getString(c.getColumnIndex("street"));
	// pwds.add(pwd);
	// }
	// c.close();
	// return pwds;
	// }

	// 查询指定fund数据data
	public Cursor fetchFromDate(String date) throws SQLException {
		String sql = "SELECT * FROM " + TABLE_MESSAGE + " WHERE " + "date"
				+ " = ?" + " order by _id desc";
		Cursor cursor = db.rawQuery(sql, new String[] { date });
		return cursor;
	}

	/**
	 * close database
	 */
	public void closeDB() {
		db.close();
	}
}
